﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;
using System.IO;
using System.Web;
using FrameworkManapro.Utilities;
using FrameworkManapro.BusinessModel;
using GMAC.ReportingSystem.Business.Entities;
using GMAC.ReportingSystem.Business.Entities.Configuration;
using FrameworkManapro.WebUI;

namespace GMAC.ReportingSystem.DataAccess
{
    public class AdditionalInformationDealerDataAccess
    {

        public void GenerateUploadFile(int UserId)
        {
            ArrayList parameters = new ArrayList { UserId };
            DataAccessComponent.ExecuteOperation("procAdditionalInformationDealerMerge", parameters);
        } // GenerateUploadFile

        public StringAdditionalInformationDealerCollection GetAdditionalInformationDealers(LoadFileStatus status, DateTime processDate, int user, PagingDetails paging, out int totalRecords)
        {
            StringAdditionalInformationDealerCollection Records = new StringAdditionalInformationDealerCollection();
            StringAdditionalInformationDealer Record;
            ArrayList parameters = new ArrayList { status, processDate, user, paging.PageNumber, paging.PageSize, paging.SortBy, paging.SortDirection };
            DataSet ds = DataAccessComponent.ExecuteMultipleSelect("procTemporaryAdditionalInformationDealerByFilter", parameters);
            // Obtiene solo X registros de la consulta por pagina
            DataTable dt = ds.Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    Record = new StringAdditionalInformationDealer
                    {
                        AdditionalInformationDealerNumber = row["AdditionalInformationDealerNumber"].ToString(),
                        AdditionalInformationDealerMonth = row["AdditionalInformationDealerMonth"].ToString(),
                        AdditionalInformationDealerGMACAcademy = row["AdditionalInformationDealerGMACAcademy"].ToString(),
                        AdditionalInformationDealerTwiter = row["AdditionalInformationDealerTwiter"].ToString(),
                        LoadDate = (DateTime)row["LoadDate"],
                        RejectionReason = row["RejectionReason"].ToString()
                        //RejectionReason_Small = (row["RejectionReason"].ToString().Length > 50) ? row["RejectionReason"].ToString().Substring(50) : row["RejectionReason"].ToString()
                    }; // Record = new StringAdditionalInformationDealer

                    Records.Add(Record);
                } // foreach
            } // if

            // Obtiene la cantida de registros totales de la consulta sin hacer pagineo
            totalRecords = int.Parse(ds.Tables[1].Rows[0][0].ToString());

            return Records;
        } // GetAdditionalInformationDealers

        /// <summary>
        /// Inserta en una tabla temporal los registros del archivo excel
        /// </summary>
        /// <param name="processDate">Fecha en la que se esta procesando el archivo</param>
        /// <param name="UserId">Codigo del usuario que esta procesando el archivo</param>
        /// <param name="Records">Coleccion de registros a insertar en la tabla temporal</param>
        public void UploadLoanFile(DateTime processDate, int userId, StringAdditionalInformationDealerCollection records, PagingDetails pagingSuccessful, PagingDetails pagingFailed, ref StringAdditionalInformationDealerCollection invalids, ref StringAdditionalInformationDealerCollection valids, out int totalRecordsSuccessful, out int totalRecordsFailed)
        {
            try
            {
                DataRow row;
                DataTable dt = new DataTable();


                dt.Columns.Add("AdditionalInformationDealerNumber", typeof(string));
                dt.Columns.Add("AdditionalInformationDealerMonth", typeof(string));
                dt.Columns.Add("AdditionalInformationDealerGMACAcademy", typeof(string));
                dt.Columns.Add("AdditionalInformationDealerTwiter", typeof(string));
                dt.Columns.Add("LoadDate", typeof(DateTime));
                dt.Columns.Add("UserID", typeof(string));

                foreach (StringAdditionalInformationDealer Record in records)
                {
                    row = dt.NewRow();

                    row["AdditionalInformationDealerNumber"] = Record.AdditionalInformationDealerNumber;
                    row["AdditionalInformationDealerMonth"] = Record.AdditionalInformationDealerMonth;
                    row["AdditionalInformationDealerGMACAcademy"] = Record.AdditionalInformationDealerGMACAcademy;
                    row["AdditionalInformationDealerTwiter"] = Record.AdditionalInformationDealerTwiter;
                    row["LoadDate"] = Record.LoadDate;
                    row["UserID"] = userId;

                    // Inserta el registro como Correcto
                    // y despues se ejecuta la validacion en el SP que puede cambiar el estatus a invalido
                    //row["bitStatus"]            = OK;

                    dt.Rows.Add(row);
                } // foreach

                // asigna los parametros para llamar el SP
                var parameters = new ArrayList { processDate, userId, dt };

                // Llama el SP que inserta en la tabla temporal
                DataAccessComponent.ExecuteOperation("procTemporaryAdditionalInformationDealerInsert", parameters);

                // Llama los metodos que obtiene la lista de registros validos e invalidos para retornarlos en la funcion
                invalids = GetAdditionalInformationDealers(LoadFileStatus.Invalid, processDate, userId, pagingFailed, out totalRecordsFailed);
                valids = GetAdditionalInformationDealers(LoadFileStatus.Valid, processDate, userId, pagingSuccessful, out totalRecordsSuccessful);

                //// Obtiene la cantidad de registros validos e invalidos
                //// esta cantidad de registros no se traen de la BD 
                //// ya que no se estan aplicando filtros que traigan menos registros,
                //// siempre trae la misma cantidad de registros por archivo
                //totalRecordsFailed = invalids.Count;
                //totalRecordsSuccessful = valids.Count;

            } // try
            catch (Exception)
            {
                throw;
            } // catch


        } // UploadLoanFile


        /// <summary>
        /// Recibe una coleccion y la retorna ordenada segun indiquen los parametros
        /// </summary>
        /// <param name="records">Coleccion de valores a ordenar</param>
        /// <param name="column">Campo por el que va a ordenar</param>
        /// <param name="order">"ASC" o "DESC"</param>
        /// <returns>Lista ordenada</returns>
        public StringAdditionalInformationDealerCollection sord(StringAdditionalInformationDealerCollection records, string column, string order)
        {
            StringAdditionalInformationDealerCollection Result = new StringAdditionalInformationDealerCollection();
            StringAdditionalInformationDealer NewRecord;
            DataRow row;
            DataTable dt = new DataTable();

            dt.Columns.Add("AdditionalInformationDealerNumber", typeof(string));
            dt.Columns.Add("AdditionalInformationDealerMonth", typeof(string));
            dt.Columns.Add("AdditionalInformationDealerGMACAcademy", typeof(string));
            dt.Columns.Add("AdditionalInformationDealerTwiter", typeof(string));
            dt.Columns.Add("LoadDate", typeof(DateTime));
            dt.Columns.Add("UserID", typeof(int));
            dt.Columns.Add("RejectionReason", typeof(string));
            dt.Columns.Add("RejectionReason_Small", typeof(string));

            // Captura todos los registros de la coleccion en la tabla para ordenarlos
            foreach (StringAdditionalInformationDealer Record in records)
            {
                row = dt.NewRow();

                row["AdditionalInformationDealerNumber"]        = Record.AdditionalInformationDealerNumber;
                row["AdditionalInformationDealerMonth"]         = Record.AdditionalInformationDealerMonth;
                row["AdditionalInformationDealerGMACAcademy"]   = Record.AdditionalInformationDealerGMACAcademy;
                row["AdditionalInformationDealerTwiter"]        = Record.AdditionalInformationDealerTwiter;
                row["LoadDate"]                                 = Record.LoadDate;
                row["UserID"]                                   = Record.UserID;
                row["RejectionReason"]                          = Record.RejectionReason;
                row["RejectionReason_Small"]                    = Record.RejectionReason_Small;

                dt.Rows.Add(row);
            } // foreach


            // Ordena los valores
            dt.DefaultView.Sort = column + " " + order;

            dt = dt.DefaultView.ToTable();

            // vuelve a asignar los registros en la coleccion con el nuevo orden
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow newrow in dt.Rows)
                {
                    NewRecord = new StringAdditionalInformationDealer
                    {
                        AdditionalInformationDealerNumber       = newrow["AdditionalInformationDealerNumber"].ToString(),
                        AdditionalInformationDealerMonth        = newrow["AdditionalInformationDealerMonth"].ToString(),
                        AdditionalInformationDealerGMACAcademy  = newrow["AdditionalInformationDealerGMACAcademy"].ToString(),
                        AdditionalInformationDealerTwiter       = newrow["AdditionalInformationDealerTwiter"].ToString(),
                        LoadDate                                = (DateTime)newrow["LoadDate"],
                        UserID                                  = (int)newrow["UserID"],
                        RejectionReason                         = newrow["RejectionReason"].ToString()
                        //RejectionReason_Small                   = newrow["RejectionReason_Small"].ToString()

                    }; // Record = new StringAdditionalInformationDealer

                    Result.Add(NewRecord);
                } // foreach
            } // if

            // Retorna los valores en la coleccion ordenados
            return Result;

        } // sord


    } // class
} // namespace
